package service;

import dao.ImportDao;
import entity.Litigation;
import entity.LitigationBorrowerDetailed;
import model.BorrowDataModel;
import model.BorrowerAnn;
import model.DataModel;
import model.ImportModel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import util.ConverterTool;
import util.ReflectTool;

import javax.persistence.Column;
import javax.persistence.Table;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Function;

public class ExeclService {

    /**
     * excel数据读取
     * @param path
     * @param obj
     * @return
     */
    public static ArrayList importExcel(String path, Object obj) {
        ArrayList<Object> list = new ArrayList<Object>();
        try {
            File file = new File(path);
            String fileName = file.getName();
            String prefix = fileName.substring(fileName.lastIndexOf(".") + 1);
            Workbook workbook = null;
            InputStream is = new FileInputStream(file);
            if (prefix.toLowerCase().equals("xls")) {
                workbook = new HSSFWorkbook(is);
            } else {
                workbook = new XSSFWorkbook(is);
            }
            Sheet sheet = workbook.getSheetAt(0);
            int businessIdCellIndex = 0;
            Class c = obj.getClass();
            Map<String, Integer> titleMap = new HashMap<>();
            Row firstRow = sheet.getRow(0);
            for (int j = 0; j <=firstRow.getPhysicalNumberOfCells() ; j++) {
                Cell cell = firstRow.getCell(j);
                if (cell != null) {
                    String cellContent = cell.getStringCellValue();
                    titleMap.put(cellContent.trim(), j);
                }
            }
            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                Object tObj = c.getDeclaredConstructor().newInstance();
                Row row = sheet.getRow(i);
                for (Field field : c.getDeclaredFields()) {
                    Integer cellIndex = titleMap.get(field.getName());
                    if (cellIndex != null) {
                        Cell cell = row.getCell(cellIndex);
                        if (cell != null) {
                            String cellContent = cell.toString();
                            String setName = ReflectTool.getFieldSetName(field);
                            Method method = c.getMethod(setName, String.class);
                            method.invoke(tObj, cellContent);
                        }
                    }
                }
                Class superClass = c.getSuperclass();
                for (Field field : superClass.getDeclaredFields()) {
                    Integer cellIndex = titleMap.get(field.getName());
                    if (cellIndex != null) {
                        Cell cell = row.getCell(cellIndex);
                        if (cell != null) {
                            String cellContent = cell.toString();
                            String setName = ReflectTool.getFieldSetName(field);
                            Method method = c.getMethod(setName, String.class);
                            method.invoke(tObj, cellContent);
                        }
                    }
                }
                list.add(tObj);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return list;
        //return filter(list);
    }

    /**
     * 借款人信息数据构建
     * @param dataModel
     * @return
     */
    public static ArrayList<LitigationBorrowerDetailed> gainBorrower(DataModel dataModel) {
        Class c = dataModel.getClass().getSuperclass();
        ArrayList<LitigationBorrowerDetailed> litigationBorrowerDetailedList = new ArrayList<LitigationBorrowerDetailed>();
        Function<BorrowDataModel, LitigationBorrowerDetailed> buildBorrower = (borrowModel) -> {
            LitigationBorrowerDetailed borrowerDetailed = new LitigationBorrowerDetailed();
            borrowerDetailed.setBusinessId(dataModel.getBusinessId());
            borrowerDetailed.setCreateUser("线下数据补录");
            borrowerDetailed.setCreateTime(new Date());
            borrowerDetailed.setType(borrowModel.getType());
            borrowerDetailed.setName(borrowModel.getName());
            borrowerDetailed.setIdentifyCard(borrowModel.getIdCard());
            borrowerDetailed.setId("UUID()");
            return borrowerDetailed;
        };
        BorrowDataModel borrowDataModel = new BorrowDataModel();
        borrowDataModel.setIdCard(dataModel.getCustomerIdentifyCard());
        borrowDataModel.setName(dataModel.getCustomerName());
        borrowDataModel.setType(1);
        litigationBorrowerDetailedList.add(buildBorrower.apply(borrowDataModel));
        for (Field field : c.getDeclaredFields()) {
            if (field.isAnnotationPresent(BorrowerAnn.class)) {
                BorrowDataModel borrowModel = new BorrowDataModel();
                borrowModel.setIdCard("");
                String customerName = String.valueOf(ReflectTool.getFieldValue(dataModel, field));
                borrowModel.setName(customerName);
                BorrowerAnn ann = field.getAnnotation(BorrowerAnn.class);
                borrowModel.setType(ann.borrowType().getTypeValue());
                litigationBorrowerDetailedList.add(buildBorrower.apply(borrowModel));
            }
        }
        return litigationBorrowerDetailedList;
    }

    /**
     * 构建主表数据
     * @param dataModel
     * @return
     */
    public static Litigation buildLitigationObj(DataModel dataModel) {
        Litigation litigation = new Litigation();
        litigation.setBusinessId(dataModel.getBusinessId());
        litigation.setBusinessType(dataModel.getBusinessType());
        litigation.setBusinessTypeName(dataModel.getBusinessType());
        litigation.setBusinessCompanyId(dataModel.getBusinessCompany());
        litigation.setBusinessCompanyName(dataModel.getBusinessCompany());
        litigation.setCustomerIdentifyCard(dataModel.getCustomerIdentifyCard());
        Double borrowMoney = Double.parseDouble(dataModel.getFinalBorrowMoney() == null ? "0" : dataModel.getFinalBorrowMoney());
        litigation.setFinalBorrowMoney(BigDecimal.valueOf(borrowMoney));
        litigation.setBorrowLimit(ConverterTool.parseInt(dataModel.getBorrowLimit()));
        litigation.setRepaymentTypeId(ConverterTool.parseInt(dataModel.getRepaymentTypeId()));
        litigation.setRepaymentPeriods(ConverterTool.parseInt(dataModel.getRepaymentPrincipa()));
        litigation.setCreateUser("线下数据补录");
        return litigation;
    }

    /**
     * 将集合转换为sql
     * @param arrayList
     * @return
     */
    public static String listToInsertSql(ArrayList arrayList) {
        StringBuffer sbSql = new StringBuffer();
        try {
            if (arrayList != null) {
                boolean isFirstEach = true;
                for (Object o : arrayList) {
                    Class classItem = o.getClass();
                    if (classItem.isAnnotationPresent(Table.class)) {
                        Table table = (Table) classItem.getAnnotation(Table.class);
                        if (table != null) {
                            if (isFirstEach) {
                                sbSql.append(String.format("insert into `%s` (", table.name()));
                            }
                            StringBuffer colsNameStr = new StringBuffer();
                            StringBuffer colsValueStr = new StringBuffer();
                            for (Field field : classItem.getDeclaredFields()) {
                                if (field.isAnnotationPresent(Column.class)) {
                                    if (isFirstEach) {
                                        Column column = field.getAnnotation(Column.class);
                                        colsNameStr.append(String.format("`%s`,", column.name()));
                                    }
                                    String getName = ReflectTool.getFieldGetName(field);
                                    Method method = classItem.getMethod(getName);
                                    Object ob = method.invoke(o);
                                    if (ob != null) {
                                        String valueStr = ob.toString();
                                        if("java.util.Date".equals(field.getGenericType().getTypeName())){
                                            colsValueStr.append("NOW(),");
                                        }
                                        else if("uuid()".equals(valueStr) || "UUID()".equals(valueStr)){
                                            colsValueStr.append("UUID(),");
                                        }else {
                                            colsValueStr.append(String.format("'%s',", valueStr));
                                        }
                                    } else {
                                        colsValueStr.append("NULL,");
                                    }
                                }
                            }
                            if (isFirstEach) {
                                String colName = colsNameStr.toString().substring(0, colsNameStr.length() - 1);
                                sbSql.append(colName + " ) VALUES ");
                            }
                            String colValue = "(" + colsValueStr.toString().substring(0, colsValueStr.length() - 1) + "),";
                            sbSql.append(" \t\n" + colValue);
                            isFirstEach = false;
                        }
                    }
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        String result = "";
        if(sbSql.length()>0){
            result = sbSql.toString().substring(0, sbSql.length() - 1) + "; \t\n";
        }
        return result;
    }

    private static ArrayList filter(ArrayList arrayList) {
        ArrayList list = new ArrayList();
        List<ImportModel> importModelList = ImportDao.getImportList();
        if (arrayList != null) {
            for (Object o : arrayList) {
                DataModel dataModel = (DataModel) o;
                if (dataModel != null) {
                    for (ImportModel importModel : importModelList) {
                        if (importModel.getBusinessId().equals(dataModel.getBusinessId())) {
                            list.add(dataModel);
                            continue;
                        }
                    }
                }
            }
        }
        return list;
    }
}
